{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.\n",
    "SPDX-License-Identifier: Apache-2.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# English Premier League Teams and Stadiums\n",
    "This notebook uses a property graph containing the teams that took part in the 2019/20 English Premier League season. The graph includes information about the teams, their stadiums and the cities where they play.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check the status of our connection\n",
    "The three cells below can be used to check the version of the workbench, the current configuration, and the status of the Neptune cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_version"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%graph_notebook_config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%status"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the graph\n",
    "The cell below creates the property graph. A set of queries that can be run against the data follows in the subsequent cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "\n",
    "// Leagues\n",
    "g.addV(\"League\").\n",
    "    property(id,'EPL-2019-20').\n",
    "    property(\"name\", \"English Premier League\").\n",
    "    property(\"nickname\", \"EPL\").\n",
    "    as(\"epl\").\n",
    "// Teams\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Arsenal\").\n",
    "    property(\"name\",\"Arsenal\").\n",
    "    property(\"fullName\",\"Arsenal F. C.\",).\n",
    "    property(\"nickname\",\"The Gunners\").\n",
    "    property(\"founded\",1886).\n",
    "    as(\"arsenal\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Chelsea\").\n",
    "    property(\"name\",\"Chelsea\").\n",
    "    property(\"fullName\",\"Chelsea F.C.\").\n",
    "    property(\"nickname\", \"The Blues\").\n",
    "    property(\"founded\",1905).\n",
    "    as(\"chelsea\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Spurs\").\n",
    "    property(\"name\",\"Tottenham Hotspur\").\n",
    "    property(\"fullName\",\"Tottenham Hotspur F.C.\").\n",
    "    property(\"nickname\",\"Spurs\").\n",
    "    property(\"founded\",1882).\n",
    "    as(\"spurs\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"WestHam\").\n",
    "    property(\"name\",\"West Ham United\").\n",
    "    property(\"fullName\",\"West Ham United F.C.\").\n",
    "    property(\"nickname\",\"The Hammers\").\n",
    "    property(\"founded\",1895).\n",
    "    as(\"westham\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Liverpool\").\n",
    "    property(\"name\",\"Liverpool\").\n",
    "    property(\"fullName\",\"Liverpool F.C.\").\n",
    "    property(\"nickname\",\"The Reds\").\n",
    "    property(\"founded\",1892).\n",
    "    as(\"liverpool\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Everton\").\n",
    "    property(\"name\",\"Everton\").\n",
    "    property(\"fullName\",\"Everton F.C.\").\n",
    "    property(\"nickname\",\"The Toffees\").\n",
    "    property(\"founded\",1878).\n",
    "    as(\"everton\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"ManUtd\").\n",
    "    property(\"name\",\"Manchester United\").\n",
    "    property(\"fullName\",\"Manchester United F.C.\").\n",
    "    property(\"nickname\",\"The Red Devils\").\n",
    "    property(\"founded\",1878).\n",
    "    as(\"manutd\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"ManCity\").\n",
    "    property(\"name\",\"Manchester City\").\n",
    "    property(\"fullName\",\"Manchester City F.C\").\n",
    "    property(\"nickname\",\"The Citizens\").\n",
    "    property(\"founded\",1880).\n",
    "    as(\"mancity\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Wolves\").\n",
    "    property(\"name\", \"Wolverhampton Wanderers\" ).\n",
    "    property(\"fullName\",\"Wolverhampton Wanderers F.C\" ).\n",
    "    property(\"nickname\",\"Wolves\").\n",
    "    property(\"founded\",1877).\n",
    "    as(\"wolves\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Burnley\").\n",
    "    property(\"name\", \"Burnley\").\n",
    "    property(\"fullName\", \"Burnley F.C\").\n",
    "    property(\"nickname\", \"The Clarets\").\n",
    "    property(\"founded\", 1882).\n",
    "    as(\"burnley\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"SheffUtd\").\n",
    "    property(\"name\", \"Sheffield United\").\n",
    "    property(\"fullName\", \"Sheffield United F.C\").\n",
    "    property(\"nickname\", \"The Blades\").\n",
    "    property(\"founded\", 1889).\n",
    "    as(\"sheffutd\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"CrystalPalace\").\n",
    "    property(\"name\", \"Crystal Palace\").\n",
    "    property(\"fullName\", \"Crystal Palace F.C\").\n",
    "    property(\"nickname\", \"Eagles\").\n",
    "    property(\"founded\", 1905).\n",
    "    as(\"palace\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Southampton\").\n",
    "    property(\"name\", \"Southampton\").\n",
    "    property(\"fullName\", \"Southampton F.C\").\n",
    "    property(\"nickname\", \"The Saints\").\n",
    "    property(\"founded\", 1885).\n",
    "    as(\"soton\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Newcastle\").\n",
    "    property(\"name\", \"Newcastle United\").\n",
    "    property(\"fullName\", \"Newcastle United F.C\").\n",
    "    property(\"nickname\", \"The Magpies\").\n",
    "    property(\"founded\", 1892).\n",
    "    as(\"newcastle\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Brighton\").\n",
    "    property(\"name\", \"Brighton and Hove Albion\").\n",
    "    property(\"fullName\", \"Brighton and Hove Albion F.C\").\n",
    "    property(\"nickname\", \"Seagulls\").\n",
    "    property(\"founded\", 1901).\n",
    "    as(\"brighton\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Watford\").\n",
    "    property(\"name\", \"Watford\").\n",
    "    property(\"fullName\", \"Watford F.C.\").\n",
    "    property(\"nickname\", \"Hornets\").\n",
    "    property(\"founded\", 1898).\n",
    "    as(\"watford\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Bournemouth\").\n",
    "    property(\"name\", \"Bournemouth\").\n",
    "    property(\"fullName\", \"Bournemouth F.C.\").\n",
    "    property(\"nickname\", \"The Cherries\").\n",
    "    property(\"founded\", 1899).\n",
    "    as(\"bournemouth\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"AstonVilla\").\n",
    "    property(\"name\", \"Aston Villa\").\n",
    "    property(\"fullName\", \"Aston Villa F.C.\").\n",
    "    property(\"nickname\", \"The Villans\").\n",
    "    property(\"founded\", 1897).\n",
    "    as(\"villa\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Leicester\").\n",
    "    property(\"name\", \"Leicester City\").\n",
    "    property(\"fullName\", \"Leicester City F.C.\").\n",
    "    property(\"nickname\", \"The Foxes\").\n",
    "    property(\"founded\", 1884).\n",
    "    as(\"leicester\").\n",
    "  addV(\"Team\").\n",
    "    property(id,\"Norwich\").\n",
    "    property(\"name\", \"Norwich City\").\n",
    "    property(\"fullName\", \"Norwich City F.C.\").\n",
    "    property(\"nickname\", \"The Canaries\").\n",
    "    property(\"founded\", 1902).\n",
    "    as(\"norwich\").\n",
    "// Stadiums\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"The_Emirates\").\n",
    "    property(\"name\", \"The Emirates\").\n",
    "    property(\"capacity\", 60704).\n",
    "    property(\"opened\", 2006).\n",
    "    as(\"em\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Stamford_Bridge\").\n",
    "    property(\"name\", \"Stamford Bridge\").\n",
    "    property(\"capacity\", 40834).\n",
    "    property(\"opened\", 1877).\n",
    "    as(\"sb\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Tottenham_Hotspur_Stadium\").\n",
    "    property(\"name\", \"Tottenham Hotspur Stadium\").\n",
    "    property(\"capacity\", 62214).\n",
    "    property(\"opened\", 2019).\n",
    "    as(\"th\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"London_Stadium\").\n",
    "    property(\"name\", \"London Stadium\").\n",
    "    property(\"capacity\", 60000).\n",
    "    property(\"opened\", 2016).\n",
    "    as(\"ls\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Anfield\").\n",
    "    property(\"name\", \"Anfield\").\n",
    "    property(\"capacity\", 53394).\n",
    "    property(\"opened\", 1884).\n",
    "    as(\"af\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Goodison_Park\").\n",
    "    property(\"name\", \"Goodison Park\").\n",
    "    property(\"capacity\", 39414).\n",
    "    property(\"opened\", 1892).\n",
    "    as(\"gp\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Old_Trafford\").\n",
    "    property(\"name\", \"Old Trafford\").\n",
    "    property(\"capacity\", 75643).\n",
    "    property(\"opened\", 1910).\n",
    "    as(\"ot\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"The_Etihad\").\n",
    "    property(\"name\", \"Etihad Stadium\").\n",
    "    property(\"capacity\", 55107).\n",
    "    property(\"opened\", 2003).\n",
    "    as(\"et\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Molineux\").\n",
    "    property(\"name\", \"Molineux Stadium\").\n",
    "    property(\"capacity\", 32050).\n",
    "    property(\"opened\", 1889).\n",
    "    as(\"mo\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Turf_Moor\").\n",
    "    property(\"name\", \"Turf Moor\").\n",
    "    property(\"capacity\", 21944).\n",
    "    property(\"opened\", 1883).\n",
    "    as(\"tm\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Bramall_Lane\").\n",
    "    property(\"name\", \"Bramall Lane\").\n",
    "    property(\"capacity\", 32125).\n",
    "    property(\"opened\", 1855).\n",
    "    as(\"bl\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Selhurst_Park\").\n",
    "    property(\"name\", \"Selhurst Park Stadium\").\n",
    "    property(\"capacity\", 25486).\n",
    "    property(\"opened\", 1924).\n",
    "    as(\"sp\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"St_Marys\").\n",
    "    property(\"name\", \"St. Mary's Stadium\").\n",
    "    property(\"capacity\", 32384).\n",
    "    property(\"opened\", 2001).\n",
    "    as(\"sm\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"St_James_Park\").\n",
    "    property(\"name\", \"St. James' Park\").\n",
    "    property(\"capacity\", 52305).\n",
    "    property(\"opened\", 1880).\n",
    "    as(\"sjp\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Amex_Stadium\").\n",
    "    property(\"name\", \"American Express Community Stadium\").\n",
    "    property(\"capacity\", 30750).\n",
    "    property(\"opened\", 2011).\n",
    "    as(\"amx\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Vicarage_Road\").\n",
    "    property(\"name\", \"Vicarage Road\").\n",
    "    property(\"capacity\", 22200).\n",
    "    property(\"opened\", 1922).\n",
    "    as(\"vr\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Vitality_Stadium\").\n",
    "    property(\"name\", \"Vitality Stadium\").\n",
    "    property(\"capacity\", 11364).\n",
    "    property(\"opened\", 1910).\n",
    "    as(\"vs\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Villa_Park\").\n",
    "    property(\"name\", \"Villa Park\").\n",
    "    property(\"capacity\", 42095).\n",
    "    property(\"opened\", 1897).\n",
    "    as(\"vp\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"King_Power_Stadium\").\n",
    "    property(\"name\", \"King Power Stadium\").\n",
    "    property(\"capacity\", 32261).\n",
    "    property(\"opened\", 2002).\n",
    "    as(\"kp\").\n",
    "  addV(\"Stadium\").\n",
    "    property(id,\"Carrow_Road_Stadium\").\n",
    "    property(\"name\", \"Carrow Road Stadium\").\n",
    "    property(\"capacity\", 27359).\n",
    "    property(\"opened\", 1935).\n",
    "    as(\"cr\").\n",
    "// Cities\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_London\").\n",
    "    property(\"name\", \"London\").\n",
    "    as(\"lon\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Liverpool\").\n",
    "    property(\"name\", \"Liverpool\").\n",
    "    as(\"liv\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Manchester\").\n",
    "    property(\"name\", \"Manchester\").\n",
    "    as(\"man\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Wolverhampton\").\n",
    "    property(\"name\", \"Wolverhampton\").\n",
    "    as(\"wol\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Leicester\").\n",
    "    property(\"name\", \"Leicester\").\n",
    "    as(\"lei\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Brighton\").\n",
    "    property(\"name\", \"Brighton\").\n",
    "    as(\"bri\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Southampton\").\n",
    "    property(\"name\", \"Southampton\").\n",
    "    as(\"sou\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Newcastle\").\n",
    "    property(\"name\", \"Newcastle\").\n",
    "    as(\"nwc\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Sheffield\").\n",
    "    property(\"name\", \"Sheffield\").\n",
    "    as(\"shf\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Birmingham\").\n",
    "    property(\"name\", \"Birmingham\").\n",
    "    as(\"bmx\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Watford\").\n",
    "    property(\"name\", \"Watford\").\n",
    "    as(\"wat\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Norwich\").\n",
    "    property(\"name\", \"Norwich\").\n",
    "    as(\"nor\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Bournemouth\").\n",
    "    property(\"name\", \"Bournemouth\").\n",
    "    as(\"bou\").\n",
    "  addV(\"City\").\n",
    "    property(id,\"City_Burnley\").\n",
    "    property(\"name\", \"Burnley\").   \n",
    "    as(\"bur\").\n",
    "// Edges for EPL membership, stadium and city\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"arsenal\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"arsenal\").to(\"em\").\n",
    "  addE(\"CITY\").from(\"em\").to(\"lon\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"chelsea\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"chelsea\").to(\"sb\").\n",
    "  addE(\"CITY\").from(\"sb\").to(\"lon\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"spurs\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"spurs\").to(\"th\").\n",
    "  addE(\"CITY\").from(\"th\").to(\"lon\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"westham\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"westham\").to(\"ls\").\n",
    "  addE(\"CITY\").from(\"ls\").to(\"lon\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"liverpool\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"liverpool\").to(\"af\").\n",
    "  addE(\"CITY\").from(\"af\").to(\"liv\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"everton\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"everton\").to(\"gp\").\n",
    "  addE(\"CITY\").from(\"gp\").to(\"liv\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"manutd\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"manutd\").to(\"ot\").\n",
    "  addE(\"CITY\").from(\"ot\").to(\"man\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"mancity\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"mancity\").to(\"et\").\n",
    "  addE(\"CITY\").from(\"et\").to(\"man\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"wolves\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"wolves\").to(\"mo\").\n",
    "  addE(\"CITY\").from(\"mo\").to(\"wol\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"burnley\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"burnley\").to(\"tm\").\n",
    "  addE(\"CITY\").from(\"tm\").to(\"bur\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"sheffutd\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"sheffutd\").to(\"bl\").\n",
    "  addE(\"CITY\").from(\"bl\").to(\"shf\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"palace\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"palace\").to(\"sp\").\n",
    "  addE(\"CITY\").from(\"sp\").to(\"lon\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"soton\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"soton\").to(\"sm\").\n",
    "  addE(\"CITY\").from(\"sm\").to(\"sou\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"newcastle\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"newcastle\").to(\"sjp\").\n",
    "  addE(\"CITY\").from(\"sjp\").to(\"nwc\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"watford\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"watford\").to(\"vr\").\n",
    "  addE(\"CITY\").from(\"vr\").to(\"wat\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"leicester\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"leicester\").to(\"kp\").\n",
    "  addE(\"CITY\").from(\"kp\").to(\"lei\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"villa\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"villa\").to(\"vp\").\n",
    "  addE(\"CITY\").from(\"vp\").to(\"bmx\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"brighton\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"brighton\").to(\"amx\").\n",
    "  addE(\"CITY\").from(\"amx\").to(\"bri\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"bournemouth\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"bournemouth\").to(\"vs\").\n",
    "  addE(\"CITY\").from(\"vs\").to(\"bou\").\n",
    "  addE(\"CURRENT_LEAGUE\").from(\"norwich\").to(\"epl\").\n",
    "  addE(\"STADIUM\").from(\"norwich\").to(\"cr\").\n",
    "  addE(\"CITY\").from(\"cr\").to(\"nor\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Verify the data was inserted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().groupCount().by(label).unfold()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.E().groupCount().by(label).unfold()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Build a visualization of the graph\n",
    "Run the cell below and select the Graph tab to see a visualization of the results. The various hints you can provide and settings you can adjust when building visualizations are explained in the blog post located [here](https://aws.amazon.com/blogs/database/visualize-query-results-using-the-amazon-neptune-workbench/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin -p v,ine,outv,oute,inv,oute,inv\n",
    "g.V().hasLabel('League').inE().outV().outE().inV().outE().inV().path().by('name').by(label)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Delete the entire data set\n",
    "This cell can be used to delete the data set. The drop is done using explicit IDs to try and avoid conflicts with any other data you may have loaded in your graph."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasId('Arsenal', 'Chelsea', 'Spurs', 'WestHam', 'Liverpool', 'Everton', 'ManUtd', 'ManCity', \n",
    "             'Wolves', 'Burnley', 'SheffUtd', 'CrystalPalace', 'Southampton', 'Newcastle', 'Brighton',\n",
    "             'Watford', 'Bournemouth', 'AstonVilla', 'Leicester', 'Norwich', 'The_Emirates', \n",
    "             'Stamford_Bridge', 'Tottenham_Hotspur_Stadium', 'London_Stadium', 'Anfield', 'Goodison_Park', \n",
    "             'Old_Trafford', 'The_Etihad', 'Molineux', 'Turf_Moor', 'Bramall_Lane', 'Selhurst_Park', \n",
    "             'St_Marys', 'St_James_Park', 'Amex_Stadium', 'Vicarage_Road', 'Vitality_Stadium', 'Villa_Park',\n",
    "             'King_Power_Stadium', 'Carrow_Road_Stadium', 'City_London', 'City_Liverpool', 'City_Manchester', \n",
    "             'City_Wolverhampton', 'City_Leicester', 'City_Brighton', 'City_Southampton', 'City_Newcastle', \n",
    "             'City_Sheffield', 'City_Birmingham', 'City_Watford', 'City_Norwich', 'City_Bournemouth', \n",
    "             'City_Burnley', 'EPL-2019-20').drop()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many teams were in the league that season?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Team').count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V('EPL-2019-20').in('CURRENT_LEAGUE').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team info\n",
    "Find the teams in the graph and their properties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Team').valueMap()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team and stadium info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Team').\n",
    "  project('name','stadium','city').\n",
    "    by('name').\n",
    "    by(out('STADIUM').values('name')).\n",
    "    by(out('STADIUM').out('CITY').values('name'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams based in London"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().has('City','name','London').in('CITY').in('STADIUM').values('name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stadiums in London"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin -p v,outv\n",
    "g.V().has('City','name','London').in('CITY').path().by('name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Teams in London plus their stadiums"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "%%gremlin -p v,outv,outv\n",
    "g.V().has('City','name','London').in('CITY').in('STADIUM').path().by('name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Was Coventry in the league that season?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().has('Team','name','Coventry').\n",
    "  fold().\n",
    "  coalesce(unfold(),constant('Not in the EPL that year'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using text predicates to find sub-strings\n",
    "This query looks for any teams that have the string \"ou\" in their name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().has('Team','name',containing('ou')).values('name')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Find everything the graph contains related to Arsenal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "\n",
    "g.V().hasId('Arsenal').\n",
    "        project('names-and-age','stadium','city').\n",
    "          by(valueMap().by(unfold())).\n",
    "          by(out('STADIUM').values('name')).\n",
    "          by(out('STADIUM').out('CITY').values('name')).unfold()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How many stadiums are in each city?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Stadium').\n",
    "  groupCount().\n",
    "    by(out('CITY')).\n",
    "  order(local).\n",
    "    by(values,desc).\n",
    "  unfold()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What year did each stadium open?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Stadium').\n",
    "  valueMap('name','opened').by(unfold()).\n",
    "    order().\n",
    "  by(select('opened'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stadiums ordered by descending capacity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Stadium').\n",
    "  order().\n",
    "    by('capacity',desc).\n",
    "  valueMap('name','capacity').\n",
    "    by(unfold())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of teams founded in a given year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin\n",
    "g.V().hasLabel('Team').\n",
    "  group().\n",
    "    by('founded').\n",
    "    by('name').\n",
    "  order(local).\n",
    "    by(keys).\n",
    "  unfold()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Page Width\n",
    "If you would like Jupyter to maximise the horizontal screen real estate run the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
